package com.cpimex.epricelite.action;
import com.cpimex.epricelite.action.Correo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Calendar;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.sql.Date;
import javax.naming.InitialContext;

import com.cpimex.epricelite.action.conexion;

public class solicitud {

	public int nsolicitud;
	public int id_sol;
	public String ruta; 
	public int vendedor; 
	public String cliente;
	public String canalD;
	public String clase_prod;
	public int nproductos;
	public String moneda_cli;
	public String codigo_vendedor;
	public String tipo_cambio;
	public String grupo_cliente;
	public String billto_cliente;
	public String diasCredito;
	public String fechaSol;
	public String AccionSol;
	public String GerenteRegional;
	public String codigo_pro[]= new String[30];
	public String consumo[]= new String[30];
	public String precio[]= new String[30];
	public String flete[]= new String[30];
	public String subseg[]= new String[30];
	public String log[]= new String[30];
	public String fecini[]= new String[30];
	public String fecfin[]= new String[30];
	public String clase_pro[]= new String[30];
	public String eliminado[]= new String[30];
	public String precioAct_detSol[]= new String[30];
	public String fleteAct_detSol[]= new String[30];
	public String fleteLista_detSol[]= new String[30];
	public String precioCliProds_detSol[]= new String[30];
	public String precioClisProds_detSol[]= new String[30];
	public String precioLista_detSol[]= new String[30];
	public String fechaInicioAct_detSol[]= new String[30];
	public String fechaExpAct_detSol[]= new String[30];
	public String diasCreditoAct_detSol[]= new String[30];
	
	public String unidad_prod[]= new String[30];
	public String division_prod[]= new String[30];
	
	
	public solicitud(){
		nproductos=0;
		cliente="";
	}
	
	private static NumberFormat nf4 = NumberFormat.getInstance();
	static {
		nf4.setMaximumFractionDigits(4);
		nf4.setMinimumFractionDigits(4);
	}
	
	public String ObtenerDatosVendedor(String strNumeroNomina,String str_usuario) throws Exception {
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		
		String str_query =
	        " SELECT a.login_usuario AS Login, " +
	        "        a.nomina_usuario AS NumeroNomina, " +
	        "        a.nombre_usuario AS Nombre, " +
	        "        a.id_perfil AS IdPerfil, " +
	        "        a.nombre_perfil AS NombrePerfil, " +
	        "        a.email_usuario AS Email, " +
	        "		 a.ruta_usuario AS Ruta " +
	        " FROM   ep_vw_usuario a " +
	        " WHERE  nomina_usuario = '" + strNumeroNomina + "' OR login_usuario ='"+ str_usuario +"' ";
	
		ResultSet rs = stmt.executeQuery(str_query);
		String resultado="";	
		while (rs.next()) {
			resultado = "Vendedor: "+rs.getString("Nombre")+"<br> " +
						"Ruta: "+rs.getString("Ruta")+"<br>";
			this.setRuta(rs.getString("Ruta"));
		}
		conn_eprice.close();
		return resultado;
	}
	
	
	
	public void setVendedor(String cvend){
		codigo_vendedor = cvend;
	}
	
	public String getVendedor(){
		return  codigo_vendedor;
	}
	
	public void setFechaSol(String fecha){
		fechaSol = fecha;
	}
	
	public String getFechaSol(){
		return  fechaSol;
	}
	
	public void setnSolicitud(int nsol){
		nsolicitud = nsol;
	}
	
	public int getnSolicitud(){
		return  nsolicitud;
	}
	
	public void setRuta(String rut){
		ruta = rut;
	}
	
	public String getRuta(){
		return ruta;
	}
	
	public void setCliente(String codigo){
		cliente = codigo;
	}
	
	public String getCliente(){
		return cliente;
	}
	
	public String getDesCliente() throws Exception{
		String descliente="";
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		String str_query ="SELECT descripcion_cli,moneda_cli,grupo_cli,billto,condiciones_cli,canal_distribucion,vendedor_cli FROM ep_vw_cliente WHERE codigo_cli= "+cliente;
		ResultSet rs = stmt.executeQuery(str_query);
		while (rs.next()){
			descliente = rs.getString("descripcion_cli");
			moneda_cli = rs.getString("moneda_cli");
			grupo_cliente = rs.getString("grupo_cli");
			billto_cliente = rs.getString("billto");
			diasCredito = rs.getString("condiciones_cli");
			canalD = rs.getString("vendedor_cli");
		}
		conn_eprice.close();
		return descliente;
	}
	
	public String getMoneda(){
		return moneda_cli;
	}
	
	public String getTipoCambio() throws Exception{
		if(this.AccionSol!="editar")
		{
			String tc="";
			InitialContext context = new InitialContext();	
			Connection conn_eprice = null;
			conn_eprice = conexion.conectar(context);
			Statement stmt = conn_eprice.createStatement();
			java.sql.Date date = new java.sql.Date(new java.util.Date().getTime());
			String str_query ="SELECT * FROM ep_vw_tipoCambio WHERE fecha_tipoC= '"+date+"'";
			ResultSet rs = stmt.executeQuery(str_query);
			while (rs.next()){
				tc = rs.getString("venta_tipoC");
				tipo_cambio = tc;
			}
			conn_eprice.close();
			return tc;
		}else return tipo_cambio;
	}
	
	public String getDesProducto(String codigo) throws Exception{
		String despro="";
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		String str_query ="SELECT descripcion_prod,clase_prod FROM ep_vw_producto WHERE codigo_prod= '"+codigo+"'";
		ResultSet rs = stmt.executeQuery(str_query);
		while (rs.next()){
			despro = rs.getString("descripcion_prod");
			clase_prod = rs.getString("clase_prod");
		}
		conn_eprice.close();
		return despro;
	}
	
	public String getUnidad(String codigo) throws Exception{
		String unidad="";
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		String str_query ="SELECT * FROM ep_cliente_articulo_unidad WHERE codigo_clie ="+cliente+" AND codigo_prod= '"+codigo+"'";
		ResultSet rs = stmt.executeQuery(str_query);
		
		String str_query2 ="SELECT * FROM ep_vw_producto WHERE codigo_prod= '"+codigo+"'";
		ResultSet rs2 = stmt.executeQuery(str_query2);
		while (rs.next()){
			unidad = rs.getString("codigo_unid");
			unidad_prod[nproductos] = unidad;
		}
		if(unidad.equals("")){
			rs2.first();
			unidad=rs2.getString("unidad_precio_venta");
			unidad_prod[nproductos] = unidad;
		}
		conn_eprice.close();
		return unidad;
	}
	
	public void getDivision(String subseg) throws Exception{
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		String str_query ="SELECT * FROM ep_subseg_division WHERE cod_subseg ='"+ subseg.trim() +"'";
		ResultSet rs = stmt.executeQuery(str_query);
		while (rs.next()){
			division_prod[nproductos] = rs.getString("cod_division");
		}
		conn_eprice.close();
	}
	
	public String getSubsegmento(String codigo) throws Exception{
		String sectores="";
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		String str_query ="SELECT a.*,b.descripcion_subseg FROM ep_vw_clienteProductoSubsegmento a,ep_vw_subsegmento b WHERE b.codigo_subseg = a.codigo_subseg AND a.codigo_cli ='"+cliente+"' AND a.codigo_prod= '"+codigo+"'" ;
		ResultSet rs = stmt.executeQuery(str_query);
		while (rs.next()){
			sectores = "<input type=\"hidden\" name=\"txt_subseg\" id=\"txt_subseg\" value=\""+rs.getString("codigo_subseg")+"\"><input type=\"text\" name=\"txt_subseg2\" value=\""+rs.getString("codigo_subseg")+"-"+rs.getString("descripcion_subseg")+"\" DISABLED style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
		}
		if(sectores.equals("")){
			Statement stmt2 = conn_eprice.createStatement();
			String str_query2 ="SELECT * FROM ep_vw_subsegmento ORDER BY descripcion_subseg";
			ResultSet rs2 = stmt2.executeQuery(str_query2);
			sectores ="<SELECT name=\"txt_subseg\" id=\"txt_subseg\" style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
			while (rs2.next()){
				sectores += "<option value=\" "+rs2.getString("codigo_subseg")+"\">"+rs2.getString("codigo_subseg")+"-"+rs2.getString("descripcion_subseg")+"</option>";
			}
			sectores +="</select>";
		}
		conn_eprice.close();
		return sectores;
	}
	
	public String getLogistica(String codigo) throws Exception{
		String logisticas="";
		String prod = this.clase_prod.trim();
		
		if(prod.toUpperCase().equals("AGROPRODUCTOS"))
		{
			InitialContext context = new InitialContext();	
			Connection conn_eprice = null;
			conn_eprice = conexion.conectar(context);
			Statement stmt2 = conn_eprice.createStatement();
			String str_query2 ="SELECT * FROM ep_vw_logistica ORDER BY id_logi";
			ResultSet rs2 = stmt2.executeQuery(str_query2);
			logisticas ="<SELECT name=\"txt_logistica\" style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
			while (rs2.next()){
				logisticas += "<option value=\" "+rs2.getString("id_logi").trim()+"\">"+rs2.getString("id_logi").trim()+"</option>";
			}
			logisticas +="</select>";
			conn_eprice.close();
		}else{
			logisticas = "<input type=\"hidden\" name=\"txt_logistica\" value=\"3302\"><input type=\"text\" name=\"txt_logistica2\" value=\"3302\" DISABLED style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
		}
		return logisticas;
	}
	public int getnProductos(){
		return nproductos;
	}
	
	public void setnProductos(int npro){
		nproductos = npro;
	}
	
	public void getGerReginal() throws Exception{
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt2 = conn_eprice.createStatement();
		String str_query2 ="SELECT * FROM ep_vw_InfoEjecutivoGerenteRegional WHERE Num_empleado = '" + this.codigo_vendedor+"'" ;
		ResultSet rs2 = stmt2.executeQuery(str_query2);
		while (rs2.next()){
			GerenteRegional = rs2.getString("Num_Jefe");
		}
		conn_eprice.close();
	}
	
	public int GuardarSolicitud() throws Exception{
		//int id_dsol=0;
		if(!codigo_vendedor.equals(""))
		{
			
			String query = "";
			String fecha="";
			int dia,mes,anio=0;
			Calendar c1 = Calendar.getInstance();
			dia = c1.get(Calendar.DATE);
			mes = c1.get(Calendar.MONTH) + 1;
			anio = c1.get(Calendar.YEAR);
			fecha = String.valueOf(anio)+"-"+String.valueOf(mes)+"-"+String.valueOf(dia); 
			if(this.AccionSol.equals("editar"))
			{
				query="UPDATE ep_solicitud SET grpCli_sol='"+grupo_cliente+"' , billTo_sol='"+billto_cliente+"' WHERE id_sol = '"+this.id_sol+"' ";
			}else{
				query="INSERT INTO [Eprice].[dbo].[ep_solicitud] "+
		             "([codigoVen_sol] "+
		             ",[fecha_sol] "+
		             ",[grpCli_sol] "+
		             ",[billTo_sol]) "+
		             "VALUES "+
		             "('"+ codigo_vendedor +"' "+
		             ",'"+fecha+"' "+
		             ",'"+grupo_cliente+"' "+
		             ",'"+billto_cliente+"') "; 
			}
			InitialContext context = new InitialContext();	
			Connection conn_eprice = null;
			conn_eprice = conexion.conectar(context);
			Statement stmt = conn_eprice.createStatement();
			stmt.addBatch(query);
			stmt.executeBatch();
			
			if(!this.AccionSol.equals("editar")){
				ResultSet rs = stmt.executeQuery("SELECT MAX(id_sol)as id_sol FROM ep_solicitud");
				if(rs.next()){
					this.id_sol = rs.getInt("id_sol");
				}
			}
			//Correo.solicitud(this.id_sol,1);
			conn_eprice.close();
		}
		return this.id_sol;
	}
	public void BorrarDetalles() throws Exception{
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		stmt.addBatch("DELETE FROM ep_detalleSolicitud WHERE id_sol = '"+this.id_sol+"'");
		stmt.executeBatch();
		conn_eprice.close();
	}
	
	public String GuardarDetalle() throws Exception{
		String query="";
		String query2="";
		getGerReginal();
		if(this.id_sol > 0){
			for(int i=0;i<nproductos;i++){
				if(!eliminado[i].equals("1"))
				{
					String fecha_ini[] = fecini[i].split("/");
					String fecha_exp[] = fecfin[i].split("/");
					query="INSERT INTO [Eprice].[dbo].[ep_detalleSolicitud] "+
				           "([codigoCli_detSol] "+
				           ",[codigoPro_detSol] "+
				           ",[logistica_detSol] "+
				           ",[subsegmento_detSol] "+
				           ",[precioSol_detSol] "+
				           ",[consumoEstMen_detSol] "+
				           ",[precioAct_detSol] "+
				           ",[fleteAct_detSol] "+
				           ",[fleteLista_detSol] "+
				           ",[precioCliProds_detSol] "+
				           ",[precioClisProds_detSol] "+
				           ",[precioLista_detSol] "+
				           ",[fechaInicioAct_detSol] "+
				           ",[fechaExpAct_detSol] "+
				           ",[diasCreditoAct_detSol] "+
				           ",[fechaInicio_detSol] "+
				           ",[fechaExp_detSol] "+
				           ",[diasCredito_detSol] "+
				           ",[id_sol] "+
				           ",[id_estatus] "+
				           ",[moneda_detSol] "+
				           ",[tipo_cambio] "+
				           ",[unidad_venta] "+
				           ",[estaEnSAP_detSol] "+
				           ",[division_detSol] "+
				           ",[gteRegAuto_detlSol]) "+
				          "VALUES ('"+this.cliente+"'," +
				          "'"+codigo_pro[i]+"'," +
				          "'"+log[i] +"'," +
				          "'"+subseg[i]+"'," +
				          "'"+precio[i]+"'," +
				          "'"+consumo[i] +"'," +
				          "'"+precioAct_detSol[i] +"'," +
				          "'"+fleteAct_detSol[i] +"'," +
				          "'"+fleteLista_detSol[i]+"'," +
				          "'"+precioCliProds_detSol[i]+"'," +
				          "'"+precioClisProds_detSol[i]+"'," +
				          "'"+precioLista_detSol[i]+"'," +
				          "'"+fechaInicioAct_detSol[i]+"'," +
				          "'"+fechaExpAct_detSol[i]+"'," +
				          "'"+diasCreditoAct_detSol[i]+"'," +
				          "'"+fecha_ini[2]+"-"+fecha_ini[1]+"-"+fecha_ini[0]+"'," +
				          "'"+fecha_exp[2]+"-"+fecha_exp[1]+"-"+fecha_exp[0]+"'," +
				          "'"+diasCredito+"'," +
				          "'"+String.valueOf(this.id_sol) +"'," +
				          "'1'," +
				          "'"+moneda_cli+"'," +
				          "'"+tipo_cambio+"'," +
				          "'"+unidad_prod[i]+"'," +
				          "'0'," +
				          "'"+division_prod[i]+"'," +
				          "'"+this.GerenteRegional+"')" +
				          "";	
					query2+=query;
					InitialContext context = new InitialContext();	
					Connection conn_eprice = null;
					conn_eprice = conexion.conectar(context);
					Statement stmt = conn_eprice.createStatement();
					stmt.addBatch(query);
					stmt.executeBatch();
					conn_eprice.close();
				}
			}
		}
		return query2;
	}
	public String setProducto(String codigo,String cons,String pre,String fle,String sub,String logi,String fechaini,String fechafin,String clase_prod) throws NumberFormatException, Exception{
		codigo_pro[nproductos]=codigo;
		consumo[nproductos]=cons;
		precio[nproductos]=pre;
		flete[nproductos]=fle;
		sub = sub.trim();
		subseg[nproductos]=sub;
		log[nproductos]=logi;
		fecini[nproductos]=fechaini;
		fecfin[nproductos]=fechafin;
		eliminado[nproductos] = "0";
		String str_segmento = "";
		if (sub != null && !sub.equals("")) {
			str_segmento = sub.substring(0, 2);
		}
		precioAct_detSol[nproductos]="0";
		fleteAct_detSol[nproductos]="0";
		fechaInicioAct_detSol[nproductos]="";
		fechaExpAct_detSol[nproductos]="";
		diasCreditoAct_detSol[nproductos]="";
		
		this.getPreciosActual(cliente,codigo,Integer.parseInt(logi.trim()),moneda_cli);
		if(!clase_prod.toUpperCase().equals("AGROPRODUCTOS")) {
			if( canalD!=null && canalD.startsWith("EX")){
				division_prod[nproductos] = "Exportacion";
			}else{
				this.getDivision(sub);
			}
		}else division_prod[nproductos] = clase_prod.toUpperCase();
		fleteLista_detSol[nproductos]= fle;
		precioCliProds_detSol[nproductos]= this.getPrecioClienteProductos(str_segmento,cliente,codigo);
		precioClisProds_detSol[nproductos]= this.getPrecioClientesProductos(str_segmento,cliente,codigo);
		precioLista_detSol[nproductos]= this.getPrecioClienteProducto(str_segmento,cliente,codigo,Double.parseDouble(cons));
		if(str_segmento.equals("19") || str_segmento.equals("29"))
			precioCliProds_detSol[nproductos] = precioLista_detSol[nproductos];
		//diasCredito_detSol[nproductos]= new String[30];
		if(moneda_cli.equalsIgnoreCase("USD")){
			precioLista_detSol[nproductos]   = nf4.format(Double.parseDouble(precioLista_detSol[nproductos])/ Double.parseDouble(tipo_cambio));
			precioCliProds_detSol[nproductos]  = nf4.format(Double.parseDouble(precioCliProds_detSol[nproductos])/Double.parseDouble(tipo_cambio));
			precioClisProds_detSol[nproductos] = nf4.format(Double.parseDouble(precioClisProds_detSol[nproductos])/Double.parseDouble(tipo_cambio));
		}else{		
			precioLista_detSol[nproductos]   = nf4.format(Double.parseDouble(precioLista_detSol[nproductos]));
			precioCliProds_detSol[nproductos]  = nf4.format(Double.parseDouble(precioCliProds_detSol[nproductos]));
			precioClisProds_detSol[nproductos] = nf4.format(Double.parseDouble(precioClisProds_detSol[nproductos]));
		}
		precio[nproductos]=nf4.format(Double.parseDouble(precio[nproductos]));
		flete[nproductos]=nf4.format(Double.parseDouble(flete[nproductos]));
		fleteLista_detSol[nproductos]=nf4.format(Double.parseDouble(fleteLista_detSol[nproductos]));
		if(!tipo_cambio.equals("") && tipo_cambio!=null) tipo_cambio=nf4.format(Double.parseDouble(tipo_cambio));
		
		precioLista_detSol[nproductos]=precioLista_detSol[nproductos].replaceAll(",","");
		precioCliProds_detSol[nproductos]=precioCliProds_detSol[nproductos].replaceAll(",","");
		precioClisProds_detSol[nproductos]=precioClisProds_detSol[nproductos].replaceAll(",","");
		precio[nproductos]=precio[nproductos].replaceAll(",","");
		flete[nproductos]=flete[nproductos].replaceAll(",","");
		fleteLista_detSol[nproductos]=fleteLista_detSol[nproductos].replaceAll(",","");
		tipo_cambio=tipo_cambio.replaceAll(",","");
		if(precioAct_detSol[nproductos].equals(""))precioAct_detSol[nproductos]="0";
		if(fleteAct_detSol[nproductos].equals(""))fleteAct_detSol[nproductos]="0";
		if(fechaInicioAct_detSol[nproductos].equals(""))fechaInicioAct_detSol[nproductos]="";
		if(fechaExpAct_detSol[nproductos].equals(""))fechaExpAct_detSol[nproductos]="";
		if(diasCreditoAct_detSol[nproductos].equals(""))diasCreditoAct_detSol[nproductos]="";
		nproductos++;
		
		return String.valueOf(nproductos);
	}
	
	public String getProductos()throws Exception{
		String texto="";
		texto+="<table border=0 cellpading=1 cellspacing=1 width=100%>";
		String color="";
		for(int i=0;i<nproductos;i++){
			if(!eliminado[i].equals("1"))
			{
				if((i%2)==0)color="EFF5EB";
				else color="EFF5EB";
				texto+="<tr><td bgcolor=FFFADF align=left><font size=1 face=arial><b>Producto:</td><td bgcolor="+color+" colspan=3 align=left><font size=1 face=arial>"+codigo_pro[i]+" - "+this.getDesProducto(codigo_pro[i])+"</td></tr>";
				clase_pro[i]=clase_prod;
				texto+="<tr><td bgcolor=FFFADF align=left><font size=1 face=arial><b>Cons.:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+consumo[i]+"</td>";
				texto+="<td bgcolor=FFFADF align=left><font size=1 face=arial><b>Precio:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+precio[i]+"</td></tr>";
				texto+="<tr><td bgcolor=FFFADF align=left><font size=1 face=arial><b>Flete:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+flete[i]+"</td>";
				texto+="<td bgcolor=FFFADF align=left><font size=1 face=arial><b>Subseg:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+subseg[i]+"</td></tr>";
				texto+="<tr><td bgcolor=FFFADF align=left><font size=1 face=arial><b>Fec Ini:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+fecini[i]+"</td>";
				texto+="<td bgcolor=FFFADF align=left><font size=1 face=arial><b>Fec Exp:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+fecfin[i]+"</td></tr>";
				texto+="<tr><td bgcolor=FFFADF align=left><font size=1 face=arial><b>Gerente:</td><td bgcolor="+color+" colspan=3 align=left><font size=1 face=arial>"+this.getGerente(subseg[i],codigo_pro[i])+"</td></tr>";
				texto+="<tr><td bgcolor=FFFADF align=left><font size=1 face=arial><b>Log:</td><td bgcolor="+color+" align=left><font size=1 face=arial>"+log[i]+"</td>";
				texto+="<td bgcolor="+color+" align=right><font size=1 face=arial><b>Borrar:</td><td bgcolor="+color+" align=left><font size=1 face=arial><a href=\"detalles_producto.jsp?opc=borrar&indice="+i+"\"><img src=\"images/img_borrar.gif\" width=\"13\" height=\"13\" border=\"0\"></a></td></tr>";
				texto+="<tr><td bgcolor=CCE1C0 align=left colspan=4><font size=1 face=arial><br></td></tr>";
			}
		}
		texto+="</table>";
		return texto;
	}
	
	public String getFecha(String nombre){
		String fecha="";
		String value ="";
		String select ="";
		int dia,mes,anio=0;
		Calendar c1 = Calendar.getInstance();
		dia = c1.get(Calendar.DATE);
		mes = c1.get(Calendar.MONTH);
		anio = c1.get(Calendar.YEAR);
		fecha="<SELECT name=\""+nombre+"[0]\" id=\""+nombre+"0\" style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
		for (int i=1;i<=31;i++){
			if (i == dia) select = "SELECTED";
			else select = "";
			if (i<10) value = "0"+String.valueOf(i);
			else value = String.valueOf(i);
			fecha+="<option value="+value+" "+select+">"+value+"</opction> ";
		}
		fecha+="</select>";
		fecha+="<SELECT name=\""+nombre+"[1]\" id=\""+nombre+"1\" style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
		for (int i=1;i<=12;i++){
			if (i == mes+1) select = "SELECTED";
			else select = "";
			if (i<10) value = "0"+String.valueOf(i);
			else value = String.valueOf(i);
			fecha+="<option value="+value+" "+select+">"+value+"</opction> ";
		}
		fecha+="</select>";
		fecha+="<SELECT name=\""+nombre+"[2]\" id=\""+nombre+"2\" style=\"background-color:e3e3e3; border: 1px solid #666666; font-size:8pt; color: #000099\">";
		for (int i=2010;i<=2020;i++){
			if (i == anio) select = "SELECTED";
			else select = "";
			fecha+="<option value="+i+" "+select+">"+i+"</opction> ";
		}
		
		fecha+="</select>";
		return fecha;
	}
	
	public String getGerente(String subseg,String codigo_pro) throws Exception{
		if(!subseg.equals(""))subseg = subseg.trim();
		String gerente= "";
		String backup_gerente= "";
		String resultado = "";
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		this.getDesProducto(codigo_pro);
		String prod = clase_prod.trim();
		String str_query ="";
		if(prod.equals("AGROPRODUCTOS"))
		{
			str_query ="SELECT dbo.ep_vw_usuario.nombre_usuario AS codigo_gte, isnull(ep_vw_usuario_1.nombre_usuario,0) AS backup_gte " +
					"FROM dbo.ep_claseProducto " +
					"INNER JOIN dbo.ep_vw_usuario ON dbo.ep_claseProducto.codigo_gte = dbo.ep_vw_usuario.nomina_usuario " +
					"LEFT JOIN dbo.ep_vw_usuario AS ep_vw_usuario_1 ON dbo.ep_claseProducto.backup_gte = ep_vw_usuario_1.nomina_usuario " +
					"WHERE dbo.ep_claseProducto.codigo_clProd = 'Agroproductos' ";
		}else{
			str_query ="SELECT dbo.ep_vw_usuario.nombre_usuario AS codigo_gte, isnull(ep_vw_usuario_1.nombre_usuario,0) AS backup_gte "+
					  "FROM dbo.ep_subseg_division " +
					  "INNER JOIN dbo.ep_claseProducto ON dbo.ep_subseg_division.cod_division = dbo.ep_claseProducto.codigo_clProd " +
					  "INNER JOIN dbo.ep_vw_usuario ON dbo.ep_claseProducto.codigo_gte = dbo.ep_vw_usuario.nomina_usuario " +
					  "LEFT  JOIN dbo.ep_vw_usuario AS ep_vw_usuario_1 ON dbo.ep_claseProducto.backup_gte = ep_vw_usuario_1.nomina_usuario " +
					  "WHERE dbo.ep_subseg_division.cod_subseg = '"+subseg+"'";
		}
		ResultSet rs = stmt.executeQuery(str_query);
		while (rs.next()){
			gerente = rs.getString("codigo_gte");
			backup_gerente = rs.getString("backup_gte");
		}
		if(!backup_gerente.equals("0")) resultado = backup_gerente;
		else resultado = gerente;
		conn_eprice.close();
		return resultado;
	}
	
	public void borrar_producto(int indice){
		eliminado[indice]="1";
	}
	
	public void getPreciosActual( 
    		String str_cliente, 
    		String str_producto, 
    		int int_logistica, 
    		String str_moneda ) throws Exception {
        
    	// System.out.println(str_moneda);
    	
    	String str_precios =
                " select   top 1 "+
                "          isnull(precioSol_detSol,0) as precio,                 " +
                "          isnull(fleteLista_detSol,0) as flete,                 " +
                "          isnull(fechaInicio_detSol,'') as fechaInicio,          " +
                "          isnull(fechaExp_detSol,'') as fechaExp,                " +
                "          isnull(diasCredito_detSol,'') as diasCredito           " +
                " from	   ep_detalleSolicitud                         " +
                " where	   codigoCli_detSol = '"+str_cliente+"' and    " +
                "          codigoPro_detSol = '"+str_producto+"' and   " +
                "          logistica_detSol =  "+int_logistica+" and   " +
                "          id_estatus = 5                              " +
                " order by fechaInicio_detSol desc                     " ;
        
    	InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt_precios = conn_eprice.createStatement();
        ResultSet rs_precios = stmt_precios.executeQuery(str_precios);
        if( rs_precios.next() ){
            this.llenarPreciosActuales( rs_precios, str_moneda );
        }else{
        	
            rs_precios.close();
            str_precios =
                    " select isnull(precio_pv,0) as precio,                   " +
                    "        0 as flete,                            " +
                    "        isnull(fechaInicio_pv,'') as fechaInicio,         " +
                    "        isnull(fechaExp_pv,'') as fechaExp,               " +
                    "        isnull(condiciones_cli,'') as diasCredito         " +
                    " from   ep_vw_precioVigente                    " +
                    "                inner join ep_vw_cliente       " +
                    "                on codigo_cli = codigoCli_pv   " +
                    " where  codigoCli_pv = '"+str_cliente+"' and   " +
                    "        codigoPro_pv = '"+str_producto+"' and  " +
                    "        logistica_pv =  "+int_logistica+"      " +
                    " order by fechaInicio_pv desc                  ";
            
            rs_precios = stmt_precios.executeQuery(str_precios);
            if( rs_precios.next() ){
                this.llenarPreciosActuales( rs_precios, str_moneda );
            }
           
        }
        conn_eprice.close();
    }
	
	private void llenarPreciosActuales( ResultSet rs_precios, String str_moneda ) throws Exception
	{  
        
        Date   dte_fechaInicio;
        Date   dte_fechaExp;
        String precio;
        String flete;
        
        precio = nf4.format(rs_precios.getDouble("precio"));
        precioAct_detSol[nproductos] = precio.replaceAll(",","");
        //dbl_precio = Double.parseDouble(precio);
             
		
        flete = nf4.format(rs_precios.getDouble("flete"));
        fleteAct_detSol[nproductos]= flete.replaceAll(",","");
        //dbl_flete = Double.parseDouble(flete);
                
        dte_fechaInicio = rs_precios.getDate("fechaInicio");
        if( dte_fechaInicio != null ){
            //cal_fechaInicio = Calendar.getInstance();
            //cal_fechaInicio.setTime(dte_fechaInicio);
        	fechaInicioAct_detSol[nproductos] = rs_precios.getString("fechaInicio");
        }else{ fechaInicioAct_detSol[nproductos] = "";}
        dte_fechaExp = rs_precios.getDate("fechaExp");
        if( dte_fechaExp != null ){
            //cal_fechaExp = Calendar.getInstance();
            //cal_fechaExp.setTime(dte_fechaExp);
        	fechaExpAct_detSol[nproductos]=rs_precios.getString("fechaExp");
        }else fechaExpAct_detSol[nproductos] = "";
        String dias_credito = rs_precios.getString("diasCredito");
        if( dias_credito != null )
        	diasCreditoAct_detSol[nproductos] = rs_precios.getString("diasCredito");
        else diasCreditoAct_detSol[nproductos]="";
        
    }
	
	public String getPrecioClienteProducto( 
    		String str_segmento, 
    		String str_cliente, 
    		String str_producto, 
    		double dbl_toneladas ) throws Exception {
    	
        String dbl_retVal = "0";
        String str_precioCP =
            " select  sum(cantidad_precioL) as cantidad_precioL " +
            " from   (                                          " +
            "  select   cantidad_precioL                         " +
            "  from    ep_precioLista                           " +
            "  where                                            " ;
        if( dbl_toneladas == 0 ){
            str_precioCP +=
                    "  ( " +
                    "     select   (isnull( sum( toneladas_vta ), 0 )/12) as toneladas_vta " +
                    "     from     ep_vw_ventaClienteProducto                              " +
                    "     where    codigoCli_vta =  '"+ str_cliente  +"' and               " +
                    "              codigoProd_vta = '"+ str_producto +"'                   " +
                    " )                                                                    " ;
        }else{
            str_precioCP += dbl_toneladas;
        }
        str_precioCP += " between rangomin_precioL and ( case when (rangomax_precioL = -1 OR rangomax_precioL = rangomin_precioL) then 100000000 else rangomax_precioL end ) and " +
                "         codigoSeg_precioL = '"+str_segmento+"' and     " +
                "         id_grupo = (                                   " +
                "                select	top 1 id_grupo                   " +
                "                from	ep_vw_producto                   " +
                "                where	codigo_prod = '"+str_producto+"' " +
                "         )                                              " +
                "  union                                                 " +
                "  select 	costo_envase as cantidad_precioL             " +
                "  from 	ep_vw_producto                               " +
                "  where 	codigo_prod = '"+str_producto+"'             " +
                " ) as tmp                                               " ;
        
        InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt_precioCP = conn_eprice.createStatement();
        ResultSet rs_precioCP = stmt_precioCP.executeQuery(str_precioCP);
        if( rs_precioCP.next() ) {
            dbl_retVal = rs_precioCP.getString("cantidad_precioL");
        }else dbl_retVal="0";
        conn_eprice.close();
        return dbl_retVal;
    }
	
	 public String getPrecioClienteProductos( 
	    		String str_segmento, 
	    		String str_cliente, 
	    		String str_producto ) throws Exception {
	        String str_retVal = "0.0";
	        String str_precioCP =
	            " select  sum(cantidad_precioL) as cantidad_precioL "+
	            " from   ( "+
	            "  select  cantidad_precioL " +
	            "  from    ep_precioLista " +
	            "  where   ( select isnull( sum(toneladas_vta), 0 )/12 " +
	            "           from   ( " +
	            "                   select   sum(toneladas_vta) as toneladas_vta " +
	            "                   from     ep_vw_ventaClienteProducto  " +
	            "                   where    codigoCli_vta = '"+str_cliente+"'  " +
	            "                   group by mes_vta,  " +
	            "                            anio_vta " +
	            "                  ) as t "+
	            "         ) between rangomin_precioL and ( case when (rangomax_precioL = -1 OR rangomax_precioL = rangomin_precioL) then 100000000 else rangomax_precioL end ) and " +
	            "          codigoSeg_precioL = '"+str_segmento+"' and " +
	            "          id_grupo =( " +
	            "                select	top 1 id_grupo " +
	            "                from	ep_vw_producto " +
	            "                where	codigo_prod = '"+str_producto+"' " +
	            "          ) " +
	            "  union " +
	            "  select 	costo_envase as cantidad_precioL " +
	            "  from 	ep_vw_producto " +
	            "  where 	codigo_prod = '"+str_producto+"' "+
	            " ) as tmp ";
	        
	        InitialContext context = new InitialContext();	
			Connection conn_eprice = null;
			conn_eprice = conexion.conectar(context);
			Statement stmt_precioCP = conn_eprice.createStatement();
	        ResultSet rs_precioCP = stmt_precioCP.executeQuery(str_precioCP);
	        if( rs_precioCP.next() ) {
	            str_retVal = nf4.format( rs_precioCP.getDouble("cantidad_precioL") );
	        }else str_retVal="0";
	        conn_eprice.close();
	        return str_retVal;
	    }
	
	 	public String getPrecioClientesProductos( String str_segmento, String str_cliente, String str_producto ) throws Exception 
	 	{
	       	String str_retVal = "0.0000";
	        String str_grupo =
	            " select grupo_cli "+
	            " from   ep_vw_cliente "+
	            " where  codigo_cli = '"+str_cliente+"' and "+
	            "        grupo_cli is not null and "+
	            "        ltrim(grupo_cli) <> ''";
	        InitialContext context = new InitialContext();	
			Connection conn_eprice = null;
			conn_eprice = conexion.conectar(context);
			Statement stmt_precioCP = conn_eprice.createStatement();
			ResultSet rs_grupo = stmt_precioCP.executeQuery(str_grupo);
	        if( rs_grupo.next() ){
	            String str_codGrupo = rs_grupo.getString("grupo_cli");
	            String str_precioCP =
	                    " select  sum(cantidad_precioL) as cantidad_precioL "+
	                    " from   ( "+
	                    "  select  cantidad_precioL " +
	                    "  from    ep_precioLista " +
	                    "  where   ( select isnull( avg(toneladas_vta), 0 ) " +
	                    "           from   ( " +
	                    "                   select   sum(toneladas_vta) as toneladas_vta " +
	                    "                   from     ep_vw_grupoClientes g  " +
	                    "                                  inner join ep_vw_cliente c "+
	                    "                                  on g.codigo_grpCli = c.grupo_cli and "+
	                    "                                     g.ruta_grpCli = c.vendedor_cli "+
	                    "                                  inner join ep_vw_ventaClienteProducto vcp "+
	                    "                                  on c.codigo_cli = vcp.codigoCli_vta "+
	                    "                   where    codigo_grpCli = '" + str_codGrupo + "'  " +
	                    "                   group by mes_vta,  " +
	                    "                            anio_vta " +
	                    "                  ) as t "+
	                    "         ) between rangomin_precioL and ( case when (rangomax_precioL = -1 OR rangomax_precioL = rangomin_precioL) then 100000000 else rangomax_precioL end ) and " +
	                    "          codigoSeg_precioL = '"+str_segmento+"' and " +
	                    "          id_grupo =( " +
	                    "                select	top 1 id_grupo " +
	                    "                from	ep_vw_producto " +
	                    "                where	codigo_prod = '"+str_producto+"' " +
	                    "          ) " +
	                    "  union " +
	                    "  select 	costo_envase as cantidad_precioL " +
	                    "  from 	ep_vw_producto " +
	                    "  where 	codigo_prod = '" + str_producto + "' " +
	                    " ) as tmp ";
	           
	            ResultSet rs_precioCP = stmt_precioCP.executeQuery(str_precioCP);
	            if( rs_precioCP.next() ) {
	                str_retVal = nf4.format( rs_precioCP.getDouble("cantidad_precioL") );
	            }else str_retVal="0";
	            rs_precioCP.close();
	        }else str_retVal="0";
	        conn_eprice.close();
	        return str_retVal;
	    }
	 
	 public void cargarSolicitud() throws Exception{
		 InitialContext context = new InitialContext();	
		 Connection conn_eprice = null;
		 conn_eprice = conexion.conectar(context);
		 Statement stmt = conn_eprice.createStatement();
		 SimpleDateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
		 ResultSet rs = stmt.executeQuery("SELECT * FROM ep_solicitud WHERE id_sol = "+this.id_sol);
		 int i=0;
		 if( rs.next() ){
			this.codigo_vendedor = rs.getString("codigoVen_sol");
			this.fechaSol = formato.format(rs.getDate("fecha_sol"));
			this.grupo_cliente = rs.getString("grpCli_sol");
			this.billto_cliente = rs.getString("billTo_sol");
			rs = stmt.executeQuery("SELECT * FROM ep_detalleSolicitud WHERE id_sol = "+this.id_sol);
			while (rs.next()){
				cliente = rs.getString("codigoCli_detSol");
				codigo_pro[i]= rs.getString("codigoPro_detSol");
				consumo[i]= nf4.format(rs.getDouble("consumoEstMen_detSol"));
				precio[i]= nf4.format(rs.getDouble("precioSol_detSol"));
				flete[i]= nf4.format(rs.getDouble("fleteLista_detSol"));
				
				subseg[i]= rs.getString("subsegmento_detSol");
				log[i]= rs.getString("logistica_detSol");
				fecini[i]= formato.format(rs.getDate("fechaInicio_detSol"));
				fecfin[i]= formato.format(rs.getDate("fechaExp_detSol"));
				//clase_pro[i]= rs.getString("grpCli_sol");
				eliminado[i]= "0";
				precioAct_detSol[i]= rs.getString("precioAct_detSol");
				fleteAct_detSol[i]= rs.getString("fleteAct_detSol");
				fleteLista_detSol[i]= rs.getString("fleteLista_detSol");
				precioCliProds_detSol[i]= rs.getString("precioCliProds_detSol");
				precioClisProds_detSol[i]= rs.getString("precioClisProds_detSol");
				precioLista_detSol[i]= rs.getString("precioLista_detSol");
				fechaInicioAct_detSol[i]= rs.getString("fechaInicioAct_detSol");
				fechaExpAct_detSol[i]= rs.getString("fechaExpAct_detSol");
				diasCreditoAct_detSol[i]= rs.getString("diasCreditoAct_detSol");
				unidad_prod[i]= rs.getString("unidad_venta");
				division_prod[i]= rs.getString("division_detSol");
				tipo_cambio = rs.getString("tipo_cambio");
				
				precioLista_detSol[i]=precioLista_detSol[i].replaceAll(",","");
				precioCliProds_detSol[i]=precioCliProds_detSol[i].replaceAll(",","");
				precioClisProds_detSol[i]=precioClisProds_detSol[i].replaceAll(",","");
				precio[i]=precio[i].replaceAll(",","");
				flete[i]=flete[i].replaceAll(",","");
				fleteLista_detSol[i]=fleteLista_detSol[i].replaceAll(",","");
				tipo_cambio=tipo_cambio.replaceAll(",","");
				
				i++;
			}
			this.nproductos = i;
		 }
		 conn_eprice.close();
	 }
	
	 public boolean enviarSolicitud() throws Exception {
		InitialContext context = new InitialContext();	
		Connection conn_eprice = null;
		conn_eprice = conexion.conectar(context);
		Statement stmt = conn_eprice.createStatement();
		try{
			stmt.clearBatch();
			stmt.addBatch("UPDATE ep_detalleSolicitud SET id_estatus=2 WHERE id_sol="+String.valueOf(this.id_sol));
			stmt.executeBatch();
			
			ResultSet rs = stmt.executeQuery("SELECT id_detSol,id_estatus FROM ep_detalleSolicitud WHERE id_sol="+String.valueOf(this.id_sol));
			while(rs.next()){
				Correo.detalleSolicitud(rs.getInt("id_detSol"), rs.getInt("id_estatus"),"eprice@cpimex.com");
			}
			return true;
		}catch (Exception e) {
			return false;
		}finally{
			conn_eprice.close();
		}
		
	}
}
